package SE010;

import java.io.IOException;
import java.io.PrintWriter;
import java.net.URLEncoder;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DecimalFormat;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;


/**
 * Servlet implementation class showStu
 */
@WebServlet("/showStu")
public class showStu extends HttpServlet{//显示班级中的所有学生
    public void doGet(HttpServletRequest request,HttpServletResponse response) throws ServletException,IOException {
        request.setCharacterEncoding("UTF-8");
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        String UserID = request.getParameter("UserID");
        String InviteID = request.getParameter("InviteID");
        String StudentID = null;
        String StudentName = null;
        String url = "jdbc:mysql://localhost:3306/listsystem";//与本机的3306端口建立连接，进入数据库listsystem
        String driverName = "com.mysql.jdbc.Driver";//驱动注册
        String userName = "root";
        String password = "root";
        Connection conn = null;//用于建立与数据库的连接
        try{
            Class.forName(driverName);// jdbc4.0 后无需使用这句进行驱动注册操作 
            conn = DriverManager.getConnection(url, userName, password);
        }catch(Exception e){
            System.out.println(e);
        }
        String sql = "select StudentID,StudentName from v_task_stu where TaskGroup='"+InviteID+"' group by StudentID";
        Statement sta = null;
        ResultSet rs = null;
        try{
            Class.forName(driverName);// jdbc4.0 后无需使用这句进行驱动注册操作 
            conn = DriverManager.getConnection(url, userName, password);
        }catch(Exception e){
            System.out.println(e);
        }
        try {
            if(conn != null) {
                sta = conn.createStatement();
            }
            if(sta != null && sql != null) {
                rs = sta.executeQuery(sql);
            }
            out.print("<html>");
            out.print("<head>");
            out.print("<title>学生列表</title>");
            out.print("</head>");
            out.print("<body>");
            out.print("<center>");
            int num = 1;
            while(rs != null && rs.next()) {
                StudentID = rs.getString("StudentID");
                StudentName = rs.getString("StudentName");
                out.print("<a href=\"showStuTask?UserID="+URLEncoder.encode(UserID)+"&InviteID="+URLEncoder.encode(InviteID)+"&StudentID="+URLEncoder.encode(StudentID)+"&StudentName="+URLEncoder.encode(StudentName)+"\">"+ num++ +"."+"学号:"+StudentID+"姓名:"+StudentName +"</a>"+"<br>");
              //  out.print("<a href=\"showStuTask?UserID="+UserID+"&InviteID="+InviteID+"&StudentID="+StudentID+"&StudentName="+StudentName+"\">"+ num++ +"."+"学号:"+StudentID+"姓名:"+StudentName +"</a>"+"<br>");

            }
            out.print("<input type=\"button\" value=\"按任务查看\" onclick=\"window.location.href='showTask?UserID="+URLEncoder.encode(UserID)+"&InviteID="+URLEncoder.encode(InviteID)+"'\"/>");
           // out.print("<input type=\"button\" value=\"按任务查看\" onclick=\"window.location.href='showTask?UserID="+UserID+"&InviteID="+InviteID+"'\"/>");
            out.print("<input type=\"button\" value=\"返回\" onclick=\"window.location.href='showTask10.jsp'\"/>");
            out.print("</center>");
            out.print("</form>");
            out.print("</body>");
            out.print("</html>");
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                if(rs != null)
                    rs.close();
                if(sta != null)
                    sta.close();
                if(conn != null)
                    conn.close();
                rs = null;
                sta = null;
                conn = null;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
